const db = require("./database.js")

async function selectProdList(record) {
  return new Promise((resolve, reject) => {
    let sql = "select * from prod"
    let params = []
    let query = []
    if (record.group_id) {
      query.push("group_id = ?")
      params.push(record.group_id)
    }
    if (record.key_word) {
      query.push("title like concat('%', ?, '%')")
      params.push(record.key_word)
    }
    if (query.length > 0) {
      sql += " where " + query.join(" and ")
    }
    db.all(sql, params, (err, rows) => {
      if (err) {
        reject(err);
      } else {
        resolve(rows)
      }
    });
  })
}

async function selectProdListSelections() {
  return new Promise((resolve, reject) => {
    const sql = 'select p.prod_id, p.title, p.info, p.img, p.n_price, p.o_price, s.sort from prod p left join prod_sel s on p.prod_id = s.prod_id order by s.sort asc limit 8'
    db.all(sql, (err, rows) => {
      if (err) {
        reject(err);
      } else {
        resolve(rows)
      }
    })
  })
}

async function selectProdByProdId(prod_id) {
  return new Promise((resolve, reject) => {
    const sql = 'select * from prod where prod_id = ?'
    const params = [prod_id]
    db.get(sql, params, (err, row) => {
      if (err) {
        reject(err);
      } else {
        resolve(row)
      }
    })
  })
}

async function selectProdGroupList() {
  return new Promise((resolve, reject) => {
    db.all("select * from prod_group", (err, rows) => {
      if (err) {
        reject(err);
      } else {
        resolve(rows)
      }
    })
  })
}

async function selectProdSaleByProdId(prod_id) {
  return new Promise((resolve, reject) => {
    db.all("select p.prod_id, p.sale_id, s.title from prod_sale p left join sale s on p.sale_id = s.sale_id where p.prod_id = ?", [prod_id], (err, rows) => {
      if (err) {
        reject(err);
      } else {
        resolve(rows)
      }
    })
  })
}

module.exports = {
  selectProdList, 
  selectProdListSelections,
  selectProdByProdId,
  selectProdGroupList,
  selectProdSaleByProdId
}
